Improving Power Query Transformation Performance

Course: Microsoft Power BI Training

Topic: Improving Power Query Transformation Performance

Course Link: Microsoft Power BI Training

Demo Link: Register for a Course Demo

JustAcademy Website: Visit JustAcademy

1. Introduction to Power Query Transformation Performance

Power Query is a data preparation and transformation technology used in Microsoft Power BI to connect to different data sources, clean raw data, combine datasets, and prepare structured information for analysis.

Transformation performance refers to how efficiently Power Query executes operations such as filtering rows, selecting columns, changing data types, merging tables, grouping records, and creating calculated columns.

When a query processes millions of rows or contains complex transformations, refresh times can increase significantly. Optimizing Power Query helps reduce unnecessary processing, improve refresh efficiency, control memory consumption, and create scalable reporting solutions.

1.1 Learning Objectives

• Understand how Power Query evaluates transformations.
• Identify common causes of slow refresh operations.
• Optimize query folding and source-side processing.
• Filter rows and remove unnecessary columns efficiently.
• Improve merge, append, grouping, and custom-column operations.
• Understand query dependencies, staging queries, and buffering.
• Use Query Diagnostics to investigate performance bottlenecks.
• Apply performance improvements without changing business results.

1.2 Key Factors Affecting Performance

2. Understanding Power Query Architecture

Power Query uses the M language to define data retrieval and transformation operations. In Power BI Desktop, these operations are displayed in the Applied Steps pane inside Power Query Editor.

2.1 Typical Transformation Workflow

1. Connect to a source: Connect to SQL Server, Excel, CSV, SharePoint, or another supported source.
2. Navigate: Select the required table, worksheet, file, or dataset.
3. Filter rows: Retain the records needed for reporting.
4. Select columns: Remove fields that are not required.
5. Clean data: Handle errors, nulls, duplicates, and inconsistent values according to business rules.
6. Transform data: Change types, split columns, merge tables, and calculate new values.
7. Load results: Load the prepared data into the Power BI semantic model or use it as an intermediate query.

2.2 How Query Evaluation Works

When a query is refreshed, Power Query evaluates the transformations required to produce the final result. Depending on the connector and operation, work may be performed by the data source, by the Power Query engine, or by both.

For example, a SQL Server source may be able to filter records and select columns directly. Power Query can translate compatible steps into a source query rather than retrieving all records and processing them locally.

Not every transformation can be translated to every source. Consequently, performance optimization requires understanding which operations execute at the source and which execute in Power Query.

2.3 Import and DirectQuery Considerations

3. Optimizing Query Folding

Query folding is the process of translating Power Query transformations into operations that a data source can execute. It is one of the most important techniques for optimizing queries against compatible sources.

3.1 Example of Query Folding

Suppose a database contains five million sales records, but a report requires only transactions from 2025 onward and four columns.

SELECT
    OrderDate,
    ProductID,
    Quantity,
    SalesAmount
FROM dbo.Sales
WHERE OrderDate >= '2025-01-01';

If the connector supports the required operations, Power Query may generate a source query similar to this SQL statement. The database filters the rows and returns only the selected columns.

If folding is unavailable, Power Query may need to retrieve more data and perform some transformations locally, depending on the query and connector.

3.2 Benefits of Query Folding

• Reduces unnecessary data transfer.
• Uses the source system's processing capabilities.
• Can reduce local memory and CPU requirements.
• Can improve refresh performance for large datasets.
• May allow source-side filtering, sorting, and aggregation.

3.3 How to Check Query Folding

1. Open Power BI Desktop.
2. Select Home > Transform data.
3. Select the query to investigate.
4. Right-click an Applied Step.
5. Check whether View Native Query is available for that step and connector.
6. Inspect the translated query if the option is available.
7. Compare earlier and later steps to identify where folding may stop.

The View Native Query option is not available for every connector or transformation. Its absence alone does not prove that no folding occurs. Use other available query-plan or diagnostic information when necessary.

3.4 Preserve Foldable Transformations

Filtering rows, selecting columns, and certain data-type conversions are often foldable for relational sources. Some custom functions, complex operations, and unsupported transformations may prevent subsequent steps from folding.

A useful strategy is to perform source-compatible operations early and add non-foldable operations only when required. However, the correct sequence depends on source capabilities and the actual query plan.

4. Filter Rows Early

Filtering reduces the number of records processed by subsequent operations. When a filter folds to the source, it can also reduce the amount of data transferred to Power Query.

4.1 Example: Filter Sales by Date

let
    Source = Sql.Database("ServerName", "SalesDB"),
    SalesTable = Source{[Schema="dbo", Item="Sales"]}[Data],
    FilteredRows = Table.SelectRows(
        SalesTable,
        each [OrderDate] >= #date(2025, 1, 1)
    )
in
    FilteredRows

This example assumes that the source contains a table named dbo.Sales and a compatible OrderDate column. Replace the server, database, schema, and table details with your own configuration.

4.2 Filtering Best Practices

• Filter by the required reporting period when business rules allow.
• Remove irrelevant transaction types when they are not needed.
• Validate date boundaries and null-handling behavior.
• Confirm that filters preserve required historical records.
• Check whether the filter folds to the source.
• Compare row counts before and after filtering.

5. Remove Unnecessary Columns

Unused columns can increase the amount of data transferred and processed. Removing them early can reduce intermediate data volume and the size of the resulting model.

5.1 Example: Select Required Columns

let
    Source = Excel.Workbook(
        File.Contents("C:\Data\Sales.xlsx"),
        null,
        true
    ),
    SalesData = Source{[Item="Sales", Kind="Table"]}[Data],
    SelectedColumns = Table.SelectColumns(
        SalesData,
        {"OrderDate", "ProductID", "Quantity", "SalesAmount"}
    )
in
    SelectedColumns

This example assumes that the workbook contains an Excel table named Sales and that all four columns exist.

5.2 Column Selection Best Practices

• Keep only fields required for reporting and transformations.
• Retain keys needed for joins and relationships.
• Remove temporary columns after they are no longer required.
• Avoid carrying long text fields through unnecessary steps.
• Check downstream query dependencies before removing a column.

6. Use Appropriate Data Types

Correct data types improve data consistency and prevent unnecessary conversions. They can also affect query folding and the efficiency of downstream calculations.

6.1 Avoid Repeated Conversions

Repeatedly changing a column from text to number, then back to text, creates unnecessary complexity and can cause errors.

• Inspect source data types before transforming columns.
• Set types at an appropriate stage of the query.
• Use consistent types for columns involved in merges.
• Handle locale-specific dates and decimal separators correctly.
• Retain identifiers as text when leading zeros or letters are significant.

7. Optimize Merge Queries

Merge Queries combines two tables by matching one or more columns. It is commonly used to add product names, customer details, categories, and reference information to transactional datasets.

7.1 Best Practices for Merging

• Filter both inputs to the required rows when appropriate.
• Remove unnecessary columns before the merge.
• Ensure join columns have compatible data types.
• Clean inconsistent keys according to documented business rules.
• Select the correct join kind.
• Expand only the columns needed from the second table.
• Check query folding and measure refresh performance.
• Validate row counts and unmatched records.

7.2 Common Join Types

7.3 Example: Merge Sales and Products

let
    SalesSource = Sales,
    ProductSource = Products,
    MergedTables = Table.NestedJoin(
        SalesSource,
        {"ProductID"},
        ProductSource,
        {"ProductID"},
        "ProductDetails",
        JoinKind.LeftOuter
    ),
    ExpandedProduct = Table.ExpandTableColumn(
        MergedTables,
        "ProductDetails",
        {"ProductName", "Category"},
        {"ProductName", "Category"}
    )
in
    ExpandedProduct

This example assumes Sales and Products are existing queries with a ProductID column. Only ProductName and Category are expanded from the Products table.

7.4 Prevent Unexpected Row Multiplication

If the lookup table contains several rows for the same join key, a merge may produce multiple matching rows for a single source record.

Check whether the lookup key should be unique. If duplicates are valid, define the intended business logic before merging. Do not remove duplicates blindly because they may represent legitimate records.

8. Optimize Append Operations

Append Queries combines rows from two or more tables with compatible structures. It is commonly used to combine monthly files, regional sales datasets, or historical and current transaction tables.

8.1 Append Performance Tips

• Use consistent column names and compatible data types.
• Remove unnecessary columns from source queries.
• Filter irrelevant rows before appending when appropriate.
• Avoid repeated cleanup steps across multiple sources when a shared preparation query is suitable.
• Check whether the underlying source supports efficient execution.
• Validate row counts and totals after appending.

8.2 Example: Append Monthly Sales

let
    CombinedSales = Table.Combine(
        {JanuarySales, FebruarySales, MarchSales}
    )
in
    CombinedSales

This example assumes that the three queries already exist. Table.Combine aligns columns by name, so consistent naming and types help prevent unexpected nulls or conversion problems.

9. Optimize Grouping and Aggregation

Grouping summarizes records by selected fields. It can reduce data volume when the report needs summary-level information rather than individual transactions.

9.1 Example: Group Sales by Product

let
    Source = Sales,
    GroupedSales = Table.Group(
        Source,
        {"ProductID"},
        {
            {
                "TotalSales",
                each List.Sum([SalesAmount]),
                type number
            },
            {
                "TotalQuantity",
                each List.Sum([Quantity]),
                Int64.Type
            }
        }
    )
in
    GroupedSales

This query creates one row per ProductID and calculates total sales and total quantity. It assumes that SalesAmount and Quantity contain suitable numeric values.

9.2 Aggregation Best Practices

• Group only when the required reporting level supports aggregation.
• Retain transaction detail when users need individual records.
• Check whether the grouping operation folds to the source.
• Validate summary totals against the source data.
• Avoid sorting the entire dataset before grouping unless the logic requires it.

10. Simplify Custom Columns and M Expressions

Custom columns can create useful business classifications, but complex expressions applied to large datasets may increase processing time.

10.1 Example of a Conditional Column

if [SalesAmount] >= 1000 then "High Value"
else if [SalesAmount] >= 500 then "Medium Value"
else "Low Value"

This expression classifies sales according to the supplied thresholds. Use the thresholds that match the organization's actual business rules.

10.2 Avoid Repeating Transformations

Repeated text cleaning, date parsing, and calculations can add unnecessary complexity. Define a clear transformation and reuse its result in later steps where appropriate.

For example, standardize customer codes once before using them in subsequent operations instead of repeating the same cleanup logic in several steps.

Power Query uses lazy evaluation, and referencing a previous step does not guarantee that its result is physically cached and evaluated only once.

10.3 Use Mapping Tables When Appropriate

When a query contains many nested conditions for mapping codes to categories, a mapping table may be easier to maintain. The mapping table can be merged into the main query when the keys and business rules support that approach.

Compare both approaches using the actual source and dataset. A mapping table is not automatically faster in every situation.

11. Handle Nulls, Errors, and Duplicates Carefully

Data-quality operations are essential, but cleaning steps should follow clear business rules rather than removing values indiscriminately.

11.1 Handling Null Values

• Replace null values only when a valid default exists.
• Preserve unknown values when they matter for reporting.
• Do not replace missing numeric values with zero unless zero is the correct business interpretation.
• Check whether null handling can be performed efficiently at the source.

11.2 Handling Errors

Inspect why conversion or transformation errors occur before removing or replacing them. Correct the source, handle the value explicitly, or exclude the record only when the business rules justify doing so.

11.3 Removing Duplicates

Removing duplicates is appropriate when a dataset requires unique records, such as a product lookup with one row per product key. It may be incorrect for transaction tables, where identical-looking rows can represent separate legitimate events.

12. Manage Query Dependencies and Staging Queries

Complex Power Query solutions often contain multiple queries that depend on shared source data. A well-organized structure improves readability and makes transformations easier to maintain.

12.1 Example of a Staging Structure

Source_Sales
    |
    +-- Sales_Detail
    |
    +-- Sales_Summary
    |
    +-- Sales_Quality_Check

A staging query can contain source navigation and common preparation steps. Downstream queries can then apply their own filtering, calculations, or reporting requirements.

12.2 Disable Load for Intermediate Queries

1. Open Power Query Editor.
2. Locate the intermediate query.
3. Right-click the query.
4. Disable Enable load when the query is not intended to become a model table.
5. Verify that downstream queries still refresh successfully.

Disabling load avoids creating an unnecessary model table, but it does not guarantee that a referenced query is evaluated only once.

12.3 Reference Versus Duplicate

A reference starts a new query from another query's definition, while a duplicate copies the existing query steps into a separate definition.

References can simplify shared transformation logic. Duplicates can be useful when independent transformation paths are needed. Neither approach guarantees persistent caching or a single evaluation during refresh.

13. Understand Table.Buffer and List.Buffer

Buffering functions can materialize data structures in memory during a query evaluation. They are advanced techniques and should not be used automatically as performance fixes.

13.1 Table.Buffer Example

let
    Source = Excel.CurrentWorkbook(){[Name="Sales"]}[Content],
    SelectedColumns = Table.SelectColumns(
        Source,
        {"ProductID", "SalesAmount"}
    ),
    BufferedTable = Table.Buffer(SelectedColumns)
in
    BufferedTable

This example demonstrates syntax only. Whether buffering improves performance depends on the source, table size, query structure, and evaluation behavior.

13.2 Potential Benefits and Risks

Use buffering only when diagnostics and testing show a measurable benefit. It does not create a persistent cache shared across all queries and refreshes.

14. Improve Performance with Incremental Refresh

Incremental refresh can reduce refresh workload for supported large Import models by refreshing selected portions of historical data rather than reprocessing the entire dataset every time.

14.1 Use RangeStart and RangeEnd

Incremental refresh commonly uses RangeStart and RangeEnd parameters to filter a date or datetime column. In supported environments, Power BI can manage partitions based on the configured refresh policy.

let
    Source = Sql.Database("ServerName", "SalesDB"),
    SalesTable = Source{[Schema="dbo", Item="Sales"]}[Data],
    FilteredRows = Table.SelectRows(
        SalesTable,
        each [OrderDate] >= RangeStart
            and [OrderDate] < RangeEnd
    )
in
    FilteredRows

Configure RangeStart and RangeEnd as the required parameters and use a compatible date or datetime column. Verify query folding so that the source can filter the relevant range where supported.

14.2 Incremental Refresh Best Practices

• Choose a date column that accurately represents the refresh policy.
• Use correct inclusive and exclusive date boundaries.
• Verify source-side filtering where supported.
• Set appropriate historical retention and refresh windows.
• Consider late-arriving records and updates to historical data.
• Test the refresh policy in the intended deployment environment.

15. Diagnose Slow Transformations

Performance optimization should start with measurement. A slow refresh may be caused by source queries, network latency, expensive transformations, or model loading.

15.1 Review Applied Steps

1. Open Power Query Editor.
2. Select the slow query.
3. Inspect the Applied Steps pane.
4. Identify expensive operations such as merges, sorting, grouping, or custom functions.
5. Check folding indicators where supported.
6. Make one targeted change at a time.
7. Refresh and compare the results.

15.2 Use Query Diagnostics

Query Diagnostics provides information about query evaluation and source activity in supported Power Query environments.

1. Open Power Query Editor.
2. Locate the available diagnostics commands in the Tools menu.
3. Start diagnostics using the available controls.
4. Perform the operation that needs investigation.
5. Stop diagnostics and review the generated information.
6. Identify expensive operations or repeated source requests.

Diagnostics output can be detailed. Focus on the query and operation under investigation, and compare results under reasonably consistent conditions.

15.3 Compare Performance Before and After

16. Practical Project: Optimize a Sales Query

In this project, you will prepare a sales dataset containing OrderDate, ProductID, Quantity, and SalesAmount.

16.1 Project Requirements

• Import the sales table.
• Keep only required columns.
• Filter the required reporting period.
• Set appropriate data types.
• Inspect query folding.
• Measure refresh performance.
• Validate totals and row counts.

16.2 Example Optimized Query

let
    Source = Sql.Database("ServerName", "SalesDB"),
    SalesTable = Source{[Schema="dbo", Item="Sales"]}[Data],
    SelectedColumns = Table.SelectColumns(
        SalesTable,
        {
            "OrderDate",
            "ProductID",
            "Quantity",
            "SalesAmount"
        }
    ),
    FilteredRows = Table.SelectRows(
        SelectedColumns,
        each [OrderDate] >= #date(2025, 1, 1)
    ),
    CorrectTypes = Table.TransformColumnTypes(
        FilteredRows,
        {
            {"OrderDate", type date},
            {"ProductID", Int64.Type},
            {"Quantity", Int64.Type},
            {"SalesAmount", Currency.Type}
        }
    )
in
    CorrectTypes

This query illustrates column selection, filtering, and type assignment. Adapt the server, database, table, columns, and types to your source. The performance improvement depends on the data source and the actual execution plan.

16.3 Testing Procedure

1. Record the original refresh duration.
2. Inspect the current query steps and folding behavior.
3. Apply the required column selection and row filters.
4. Check whether the transformations fold to the source.
5. Refresh the query and record the new duration.
6. Compare row counts and totals with the expected results.
7. Document the changes and measured impact.

17. Troubleshooting Common Performance Problems

18. Best Practices Checklist

• Use appropriate sources and avoid unnecessary data retrieval.
• Filter irrelevant rows early when business rules allow.
• Remove unused columns before expensive transformations.
• Use appropriate data types and avoid repeated conversions.
• Preserve query folding when supported and beneficial.
• Validate join keys before merging tables.
• Expand only the columns required from merged queries.
• Avoid unnecessary sorting, grouping, and repeated text processing.
• Organize complex queries using staging and reference queries.
• Disable load for intermediate queries that do not need model tables.
• Use Table.Buffer only when testing demonstrates a benefit.
• Consider incremental refresh for suitable large models.
• Use Query Diagnostics to investigate actual bottlenecks.
• Measure performance before and after optimization.
• Verify that optimized queries preserve data accuracy.

19. Practice Exercises

Exercise 1: Column Optimization

Import a sales dataset containing at least ten columns. Identify unused columns, remove them, and compare the query output with the original.

Exercise 2: Filtering a Large Dataset

Filter transaction data to a specific reporting period. Check query folding where supported and validate the number of returned records.

Exercise 3: Merge Optimization

Merge Sales with Products. Expand only the required product fields and investigate whether duplicate product keys increase the number of output rows.

Exercise 4: Query Folding Investigation

Create a query with multiple transformations. Identify which operations fold to the source where supported, and test the effect of changing the step sequence.

Exercise 5: Performance Comparison

Record the original refresh duration, make two or three targeted improvements, and compare refresh times. Confirm that totals and required records remain correct.

20. Interview Questions and Answers

Q1. What is Power Query?

Answer: Power Query is a data preparation technology used to connect to sources, clean data, combine datasets, and transform information for analysis.

Q2. What is query folding?

Answer: Query folding translates supported Power Query transformations into operations executed by the data source, potentially reducing data transfer and local processing.

Q3. Why should rows be filtered early?

Answer: Early filtering can reduce the amount of data processed by subsequent operations. When the filter folds to the source, it can also reduce the amount of data transferred.

Q4. Why remove unnecessary columns?

Answer: Removing unused columns can reduce data transfer, intermediate processing, and the size of the final model.

Q5. What is the difference between full and partial query folding?

Answer: Full folding means the required transformations execute at the source. Partial folding means some operations execute at the source and others execute in the Power Query engine.

Q6. Why can a merge be slow?

Answer: Large inputs, inconsistent keys, multiple matches, or expensive local processing can make merges slow.

Q7. What does Table.Buffer do?

Answer: Table.Buffer materializes a table in memory for the current evaluation. It can help in specific cases but may increase memory usage and prevent subsequent transformations from folding.

Q8. How does query folding relate to storage mode?

Answer: Folding can improve refresh performance for Import models and is particularly important for DirectQuery and Dual tables, which have specific folding requirements.

Q9. How do you diagnose a slow query?

Answer: Review Applied Steps, inspect folding indicators where available, use Query Diagnostics, and compare refresh timings under consistent conditions.

Q10. Does fewer transformation steps always mean better performance?

Answer: No. Performance depends on the source, data volume, execution plan, query folding, and transformation type rather than step count alone.

Q11. What is incremental refresh?

Answer: Incremental refresh refreshes selected portions of historical data according to a configured policy in supported Power BI models, potentially reducing the workload compared with refreshing the entire dataset.

Q12. Why validate data after optimization?

Answer: Validation confirms that performance improvements have not changed business logic, removed required records, or introduced incorrect totals.

21. Quick Revision Notes

• Power Query uses M expressions to define transformation steps.
• Query folding delegates supported operations to the data source.
• Early filtering and column selection can reduce processing requirements.
• Appropriate data types improve consistency and reduce unnecessary conversions.
• Merge performance depends on input size, key quality, and join behavior.
• Grouping can reduce data volume but may remove necessary transaction detail.
• Staging queries improve organization but do not guarantee cached evaluation.
• Buffering is a specialized technique, not a universal optimization.
• Incremental refresh can reduce refresh workload for suitable large models.
• Query Diagnostics helps investigate source activity and transformation costs.
• Always measure performance and verify data accuracy after changes.

22. Conclusion

Improving Power Query transformation performance involves reducing unnecessary data, selecting efficient transformation strategies, preserving query folding where practical, optimizing merges, and measuring the refresh process.

The most effective approach is to identify the bottleneck first, make targeted changes, compare results, and validate data accuracy. Optimization should improve refresh efficiency without compromising the completeness or correctness of business information.

Continue learning: Explore the Microsoft Power BI Training course, register for a course demo, and visit the JustAcademy website for additional learning opportunities.

23. Further Reading

• Microsoft Learn: Understanding Query Evaluation and Query Folding
• Microsoft Learn: Query Folding Guidance in Power BI Desktop
• Microsoft Learn: Query Folding Indicators
• Microsoft Learn: Query Folding Examples